package cn.ctyun.ipush.utils;

import cn.ctyun.ipush.constant.ResultInfo;
import cn.ctyun.ipush.dto.ResultDto;
import cn.ctyun.ipush.event.ToolSpring;
import cn.ctyun.ipush.model.*;
import cn.ctyun.ipush.service.YixinTokenService;
import cn.ctyun.ipush.vo.ScheduleJobVo;
import cn.ctyun.ipush.vo.TaskVo;
import com.aspose.cells.*;
import com.dexcoder.commons.utils.UUIDUtils;
import com.dexcoder.dal.JdbcDao;
import org.apache.commons.lang.StringUtils;
import org.quartz.JobExecutionException;
import org.slf4j.Logger;
import sun.misc.BASE64Decoder;

import javax.servlet.ServletInputStream;
import javax.servlet.http.HttpServletRequest;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * User: user
 * Date: 16-6-19
 * Time: 上午11:43
 * To change this template use File | Settings | File Templates.
 */
public class DataUtils {
    /**
     * 队列比较 两个list是否内容一致
     * @param <T>
     * @param a
     * @param b
     * @return
     */
    public static <T extends Comparable<T>> boolean compare(List<T> a, List<T> b) {
        if(a.size() != b.size())
            return false;
        Collections.sort(a);
        Collections.sort(b);
        for(int i=0;i<a.size();i++){
            if(!a.get(i).equals(b.get(i)))
                return false;
        }
        return true;
    }

    public static  String  getJsonFromRequest(HttpServletRequest request){
        try {
            BufferedReader br = new BufferedReader(new InputStreamReader(
                    (ServletInputStream) request.getInputStream(), "utf-8"));
            StringBuffer stringBuffer = new StringBuffer("");
            String temp;
            while ((temp = br.readLine()) != null) {
                stringBuffer.append(temp);
            }
            br.close();
            return stringBuffer.toString();
        } catch (IOException e) {
           return null;
        }
    }

    /**
     * Encode a string using Base64 encoding. Used when storing passwords as
     * cookies.
     * <p/>
     * This is weak encoding in that anyone can use the decodeString routine to
     * reverse the encoding.
     *
     * @param str
     * @return String
     * @throws IOException
     */
    public static String encodeString(String str) throws IOException {
        sun.misc.BASE64Encoder encoder = new sun.misc.BASE64Encoder();
        String encodedStr = new String(encoder.encodeBuffer(str.getBytes()));

        return encodedStr.trim();
    }

    /**
     * Decode a string using Base64 encoding.
     *
     * @param str
     * @return String
     * @throws IOException
     */
    public static String decodeString(String str) throws IOException {
        sun.misc.BASE64Decoder dec = new sun.misc.BASE64Decoder();
        String value = new String(dec.decodeBuffer(str));

        return value;
    }

    public static ResultDto isFileFormatOk(InputStream inputStream){
        ResultDto resultDto=new ResultDto();
        try{
            Workbook workbook=new Workbook(inputStream);
            resultDto.setStatus(ResultInfo.success);
            return resultDto;
        }catch (Exception ex){
            resultDto.setStatus(ResultInfo.failure);
            resultDto.setInfo(ex.getMessage());
            return resultDto;
        }
    }

    /*
    sheet页集合是否在workbook中存在
    返回：select city,salas
     */
    public static ResultDto isSheetNamesExist(InputStream inputStream,List<String> sheetList){
        ResultDto resultDto=new ResultDto();
        try {
            WorkbookDesigner workbookDesigner=new WorkbookDesigner();
            Workbook workbook=new Workbook(inputStream);
            workbookDesigner.setWorkbook(workbook);
            List<String> workSheetList=new ArrayList<String>();
            for(int i=0;i<workbook.getWorksheets().getCount();i++){
                workSheetList.add(workbook.getWorksheets().get(i).getName());
            }

            Boolean isExist=workSheetList.containsAll(sheetList);
            if(isExist){
                //获取 sql 语句
                String[] tableFields=workbookDesigner.getSmartMarkers();
                Map<String,String> sqlMap=new HashMap<String ,String>();
                if(tableFields.length<1){
                    //workBook中不存在表名
                    resultDto.setStatus(ResultInfo.failure);
                    resultDto.setInfo("Excel不存在提取数据需要的表名及字段，请修改");
                    return resultDto;
                }else{
                    for(String tableField:tableFields){
                        String tableName=tableField.split("\\.")[0].substring(2).trim();
                        if(sqlMap.containsKey(tableName)){
                            sqlMap.put(tableField.split("\\.")[0].substring(2).trim(),sqlMap.get(tableName)+","+tableField.split("\\.")[1]);
                        }else{
                            sqlMap.put(tableField.split("\\.")[0].substring(2).trim(),"select "+tableField.split("\\.")[1]);
                        }
                    }
                }
                resultDto.setStatus(ResultInfo.success);
                resultDto.setInfo(sqlMap);
                return resultDto;
            }else{
                resultDto.setStatus(ResultInfo.failure);
                resultDto.setInfo("sheet名称在workbook中不存在，请修改");
                return resultDto;
            }

        } catch (Exception e) {
            resultDto.setStatus(ResultInfo.failure);
            resultDto.setInfo(e.getMessage());
            return resultDto;
        }
    }


    //发送易信到好友
    public static  Boolean sendYixinToFriends(List<String> nicks,List<String> imgs, List<String> urls, String title,Logger LOG) {
        try{
            YixinTokenService yixinTokenService = (YixinTokenService) ToolSpring.getApplicationContext().getBean("yixinTokenService");
            YixinTokenModel yixinTokenModel = yixinTokenService.querySingleResult("100");
            YixinUtils yixinUtils = new YixinUtils();
            if(yixinUtils.initYixinToken(yixinTokenModel)){
                yixinTokenService.update(yixinUtils.yixinTokenModelStatic);
            }
            for(String img:imgs){
                yixinUtils.send(nicks, img, img, title, yixinTokenModel);
            }
        }catch (Exception ex){
            LOG.error("易信发送失败："+ex.getMessage());
            return false;
        }
        return false;
    }


    //处理JOB异常
    public static  void exceptionDo(JdbcDao jdbcDao, TaskVo taskVo, ScheduleJobVo scheduleJobVo, String currentTrigger,String message) throws JobExecutionException {
        JobExecutionException jobExecutionException = new JobExecutionException(new Exception());
        // 设置 将自动 去除 这个任务的触发器,所以这个任务不会再执行
        jobExecutionException.setUnscheduleAllTriggers(true);
        //逻辑处理
        ///作业状态变化
        TaskDetailModel taskDetailModel = new TaskDetailModel();
        taskDetailModel.setTaskDetailModelId(taskVo.getTaskDetail().getTaskDetailModelId());
        taskDetailModel.setTaskStatus("999");
        jdbcDao.update(taskDetailModel);
        ///写到日志表
        LogModel logModel = new LogModel();
        logModel.setLogModelId(UUIDUtils.getUUID32());
        logModel.setLogCreateDate(DateUtils.dateToString(new Date()));
        logModel.setLogMessage(message);
        logModel.setLogName(scheduleJobVo.getJobGroup() + "_" +  taskVo.getTaskDetail().getTaskAliasName() + "_" + currentTrigger);
        jdbcDao.save(logModel);
        // 抛出异常
        throw jobExecutionException;
    }

    public static  void exceptionDo(TaskVo taskVo, ScheduleJobVo scheduleJobVo, String  message,JdbcDao jdbcDao) {
        //逻辑处理
        ///作业状态变化
        TaskDetailModel taskDetailModel1 = new TaskDetailModel();
        taskDetailModel1.setTaskDetailModelId(taskVo.getTaskDetail().getTaskDetailModelId());
        taskDetailModel1.setTaskStatus("999");
        jdbcDao.update(taskDetailModel1);
        ///写到日志表
        LogModel logModel = new LogModel();
        logModel.setLogModelId(UUIDUtils.getUUID32());
        logModel.setLogCreateDate(DateUtils.dateToString(new Date()));
        logModel.setLogMessage(message);
        logModel.setLogName(scheduleJobVo.getJobGroup() + "_" + taskVo.getTaskDetail().getTaskAliasName() + "_" + DateUtils.timestampToDataString(System.currentTimeMillis()));
        jdbcDao.save(logModel);
    }

    /**
     * worksheet 转换图片
     *
     * @param scheduleJobVo
     * @param workbookDesigner
     * @return  返回图片和Excel文件列表
     */
    public static  Map<String,String> WorkSheetToPicture(ScheduleJobVo scheduleJobVo, WorkbookDesigner workbookDesigner,PictureModel pictureModel,Logger LOG) {
        Map<String,String> picAndExcelMap=new HashMap<String,String>();
        int sheetsCount = workbookDesigner.getWorkbook().getWorksheets().getCount();
        // picAndExcelMap.put("excel",workbookDesigner.getWorkbook().getFileName());

        ImageOrPrintOptions imageOrPrintOptions = new ImageOrPrintOptions();
        imageOrPrintOptions.setImageFormat(ImageFormat.getPng());
        imageOrPrintOptions.setOnePagePerSheet(true);
        //根据配置设置图片的分辨率
        if(null!=pictureModel){
            if(!StringUtils.isEmpty(pictureModel.getPicturePix())){
                imageOrPrintOptions.setHorizontalResolution(Integer.parseInt(pictureModel.getPicturePix()));
                imageOrPrintOptions.setVerticalResolution(Integer.parseInt(pictureModel.getPicturePix()));
            }else{
                imageOrPrintOptions.setHorizontalResolution(100);
                imageOrPrintOptions.setVerticalResolution(100);
            }
        }else{
            imageOrPrintOptions.setHorizontalResolution(100);
            imageOrPrintOptions.setVerticalResolution(100);
        }

        String sheetNames=pictureModel.getPicSheetNames();
        StringBuffer yixinPic=new StringBuffer();
        if(sheetNames!=null){
            List<String> sheetList=new ArrayList<String>();
            if(!StringUtils.isEmpty(sheetNames)){
                for (String sheetName:sheetNames.split("\\*")){
                    sheetList.add(sheetName);
                }
                StringBuffer stringBuffer = new StringBuffer();
                for (int i = 0; i < sheetsCount; i++) {
                    Worksheet worksheet = workbookDesigner.getWorkbook().getWorksheets().get(i);
                    if(sheetList.contains(worksheet.getName())){
                        worksheet.getPageSetup().setLeftMargin(0.0);
                        worksheet.getPageSetup().setRightMargin(0.0);
                        worksheet.getPageSetup().setBottomMargin(0.0);
                        worksheet.getPageSetup().setTopMargin(0.0);
                        try {
                            SheetRender sheetRender = new SheetRender(worksheet, imageOrPrintOptions);
                            String jobNameDayPath = ExcelUtils.createFileFolderByGroupAndJobName(scheduleJobVo.getJobGroup(), scheduleJobVo.getJobName());
                            String picPath = jobNameDayPath + "/" + DateUtils.timestampToDataString(System.currentTimeMillis()) + "_" + worksheet.getName() + ".png";
                            stringBuffer.append(picPath + "$");
                            sheetRender.toImage(0, picPath);

                            //将图片放在webapp下
                            LOG.info("图片放在webapp下开始");
                            String commonPath="/" + DateUtils.timestampToDataString(System.currentTimeMillis())   + ".png";
                            String picServerPath=ConfigUtils.getStringValue("pictureServerAddress")+commonPath;
                            String picYixinPath=ConfigUtils.getStringValue("pictureYixinURL")+commonPath;
                            sheetRender.toImage(0, picServerPath);
                            yixinPic.append(picYixinPath+"$");
                            LOG.info("图片放在webapp下结束");


                            LOG.info("转换的sheet页的名称是："+worksheet.getName());
                        } catch (Exception e) {
                            LOG.info(workbookDesigner.getWorkbook().getFileName() + "-" + worksheet.getName() + "转换图片异常");
                            break;
                        }
                    }
                    continue;
                }

                if (yixinPic.length() > 0) {
                    yixinPic.deleteCharAt(yixinPic.length() - 1);
                    picAndExcelMap.put("pictureYixinURL", yixinPic.toString());
                } else {
                    return null;
                }


                if (stringBuffer.length() > 0) {
                    stringBuffer.deleteCharAt(stringBuffer.length() - 1);
                    picAndExcelMap.put("picture", stringBuffer.toString());
                    return picAndExcelMap;
                } else {
                    return null;
                }
            }
        }
        LOG.error("没有选择要转换图片的sheet页名称！！！");
        return null;

    }

    /**
     * 数据源灌入到excel文件,根据Excel中配置的sql
     *
     * @param scheduleJobVo
     * @param taskVo
     * @param currentTrigger
     * @return
     */
    public static WorkbookDesigner DBDataToExcelByExcelSql(ScheduleJobVo scheduleJobVo, TaskVo taskVo, String currentTrigger,ExcelModel excelModel,Logger LOG) {
        Connection conn = null;
        String userName = taskVo.getDataSource().getUserName();
        String password = taskVo.getDataSource().getDsPassword();
        String url = taskVo.getDataSource().getDataSourceUrl();
        String jobGroup = scheduleJobVo.getJobGroup();
        String jobName = scheduleJobVo.getJobName(); // todo 替换下面


        List<Map<String, Object>> rsAndPsList = new ArrayList<Map<String, Object>>();
        try {
            conn = DBUtils.getTmpConnection(url, userName, password, jobGroup, jobName, currentTrigger);
            if (null == conn) {
                LOG.error("Error:  task time span is too short");
                return null;
            }

            //得到sql集合
            String sqlStr = taskVo.getExcel().getExcelTableFields();
            List<String> sqlList=new ArrayList<String>();
            if(sqlStr!=null){
                String[] tmpSqlList=sqlStr.split("\\$");
                for(String sql:tmpSqlList){
                    sqlList.add(sql);
                }
            }else{
                LOG.info("table params is empty");
                return null;
            }

            //得到sql对应的数据集合
            if(sqlList.size()>0){
                for(String sql:sqlList){
                    String[] sqltmp=sql.trim().split("\\s+");
                    Map<String, Object> rsMap = DBUtils.executeOracleSelectBySql(conn, sql,sqltmp[sqltmp.length-1]); // return {"ps":psmt,"rs":rs,"tableName":tableName,"sql":sql}
                    rsAndPsList.add(rsMap);
                }
            }else{
                LOG.error("data generate fail");
                return null;
            }

            //得到workDesiger
            Map<String, ResultSet> rsMap = new HashMap<String, ResultSet>();
            if (rsAndPsList.size() > 0) {
                //存放Excel模板的位置
                String TEMPLATE_FILE_PATH_PART = "excel/Book1.xlsx";//todo 换成变量
                TEMPLATE_FILE_PATH_PART=taskVo.getExcel().getExcelPath();
                if(StringUtils.isEmpty(TEMPLATE_FILE_PATH_PART)){
                    LOG.error("模板文件地址：TEMPLATE_FILE_PATH_PART 是空的");
                    return null;
                }
                for (Map<String, Object> map : rsAndPsList) {
                    rsMap.put((String) map.get("tableName"), (ResultSet) map.get("rs"));
                }
                String jobNameDayPath = ExcelUtils.createFileFolderByGroupAndJobName(jobGroup, jobName);
                WorkbookDesigner workbookDesigner = ExcelUtils.dbDataToExcelWithMulTables(rsMap, TEMPLATE_FILE_PATH_PART, jobNameDayPath,excelModel);

                LOG.info("Excel 文件生成成功");
                return workbookDesigner;
            }else{
                LOG.error("result data is empty");
                return null;
            }

        } catch (Exception ex) {
            LOG.error(ex.getMessage());
            return null;
        } finally {
            //释放执行statement and resultSet
            try {
                for (Map<String, Object> entry : rsAndPsList) {
                    ResultSet tmprs = (ResultSet) entry.get("rs");
                    if (tmprs != null && !tmprs.isClosed()) {
                        tmprs.close();
                        LOG.info("---ResutlSet has closed ");
                    }
                    java.sql.PreparedStatement preparedStatement = (java.sql.PreparedStatement) entry.get("ps");
                    if (preparedStatement != null && !preparedStatement.isClosed()){
                        preparedStatement.close();
                        LOG.info("---OraclePreparedStatement has closed ");
                    }
                }
            } catch (SQLException e) {
                LOG.error("ERROR SQL statment execute fail，message："+e.getMessage());
            }
            //释放连接conn
            try {
                if (conn != null && !conn.isClosed()){
                    conn.close();
                    LOG.info("---Connection  has closed ");
                }
            } catch (SQLException e) {
                LOG.error("---ERROR: tmp connection fail,message:"+e.getMessage());
            }
        }
    }


    /**
     * 数据源灌入到excel文件,根据数据源表中的表名获取数据
     *
     * @param scheduleJobVo
     * @param taskVo
     * @param currentTrigger
     * @return
     */
    public static WorkbookDesigner DBDataToExcel(ScheduleJobVo scheduleJobVo, TaskVo taskVo, String currentTrigger,ExcelModel excelModel,Logger LOG) {
        Connection conn = null;
        String userName = taskVo.getDataSource().getUserName();
        String password = taskVo.getDataSource().getDsPassword();
        String url = taskVo.getDataSource().getDataSourceUrl();
        String jobGroup = scheduleJobVo.getJobGroup();
        String jobName = scheduleJobVo.getJobName(); // todo 替换下面


        List<Map<String, Object>> rsAndPsList = new ArrayList<Map<String, Object>>();
        try {
            conn = DBUtils.getTmpConnection(url, userName, password, jobGroup, jobName, currentTrigger);
            if (null == conn) {
                LOG.info("Error:  task time span is too short");
                return null;
            }

            //得到表集合
            String tableStr = taskVo.getDataSource().getSqlListDb();
            List<String> tableList=new ArrayList<String>();
            if(tableStr!=null){
                String[] tmpTableList=tableStr.split("\\$");
                for(String table:tmpTableList){
                    tableList.add(table);
                }
            }else{
                LOG.info("table params is empty");
                return null;
            }

            //得到table对应的数据集合
            if(tableList.size()>0){
                for(String table:tableList){
                    Map<String, Object> rsMap = DBUtils.executeSelectByTable(conn, table); // return {"ps":psmt,"rs":rs,"tableName":tableName,"sql":sql}
                    rsAndPsList.add(rsMap);
                }
            }else{
                LOG.error("data generate fail");
                return null;
            }

            //得到workDesiger
            Map<String, ResultSet> rsMap = new HashMap<String, ResultSet>();
            if (rsAndPsList.size() > 0) {
                //存放Excel模板的位置
                String TEMPLATE_FILE_PATH_PART = "excel/Book1.xlsx";//todo 换成变量
                TEMPLATE_FILE_PATH_PART=taskVo.getExcel().getExcelPath();
                if(StringUtils.isEmpty(TEMPLATE_FILE_PATH_PART)){
                    LOG.error("模板文件地址：TEMPLATE_FILE_PATH_PART 是空的");
                    return null;
                }
                for (Map<String, Object> map : rsAndPsList) {
                    rsMap.put((String) map.get("tableName"), (ResultSet) map.get("rs"));
                }
                String jobNameDayPath = ExcelUtils.createFileFolderByGroupAndJobName(jobGroup, jobName);
                WorkbookDesigner workbookDesigner = ExcelUtils.dbDataToExcelWithMulTables(rsMap, TEMPLATE_FILE_PATH_PART, jobNameDayPath,excelModel);

                LOG.info("Excel 文件生成成功");
                return workbookDesigner;
            }else{
                LOG.error("result data is empty");
                return null;
            }

        } catch (Exception ex) {
            LOG.error(ex.getMessage());
            return null;
        } finally {
            //释放执行statement and resultSet
            try {
                for (Map<String, Object> entry : rsAndPsList) {
                    ResultSet tmprs = (ResultSet) entry.get("rs");
                    if (tmprs != null && !tmprs.isClosed()) {
                        tmprs.close();
                        LOG.info("---ResutlSet has closed ");
                    }
                    java.sql.PreparedStatement preparedStatement = (java.sql.PreparedStatement) entry.get("ps");
                    if (preparedStatement != null && !preparedStatement.isClosed()){
                        preparedStatement.close();
                        LOG.info("---OraclePreparedStatement has closed ");
                    }
                }
            } catch (SQLException e) {
                LOG.error("ERROR SQL statment execute fail，message："+e.getMessage());
            }
            //释放连接conn
            try {
                if (conn != null && !conn.isClosed()){
                    conn.close();
                    LOG.info("---Connection  has closed ");
                }
            } catch (SQLException e) {
                LOG.error("---ERROR: tmp connection fail,message:"+e.getMessage());
            }
        }
    }


    public static void iteratorDelete(Iterator<TaskDetailModel> it, String taskDetailId){
        while(it.hasNext()){
            TaskDetailModel taskDetailModel1= it.next();
            if(taskDetailModel1.getTaskDetailModelId().equals(taskDetailId)){
                it.remove();
            }
        }
    }

    public static String getFilePath(String taskDetailId) {

        return null;  //To change body of created methods use File | Settings | File Templates.
    }


    public static String getFromBASE64(String s)
    {
        if (s == null) return null;
        BASE64Decoder decoder = new BASE64Decoder();
        try
        {
            byte[] b = decoder.decodeBuffer(s);
            return new String(b);
        }
        catch (Exception e)
        {
            return null;
        }
    }




}
